package store.dao.daoImpl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import store.dao.ProductDao;
import store.domain.Product;
import store.utils.JDBCUtils;

public class ProductDaoImpl implements ProductDao {

	@Override
	public void saveProduct(Product product) throws Exception {

		String sql = "insert into product values (?,?,?,?,?,?,?,?,?,?);";
		Object [] params = {product.getPid(),product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getCid()};
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		queryRunner.update(sql,params);
	}

	@Override
	public List<Product> findHots() throws Exception{
		String sql = "select * from product where pflag = 0 and is_hot order by pdate desc limit 0,9";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanListHandler<Product>(Product.class));
	}

	@Override
	public List<Product> findNews() throws Exception{
		String sql = "select * from product where pflag=0 order by pdate desc limit 0,9";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanListHandler<Product>(Product.class));	
		
	}

	@Override
	public Product findProductByPid(String pid) throws Exception {
		String sql = "select * from product where pid = ?";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanHandler<Product>(Product.class),pid);
	}

	@Override
	public int findTotalRecords(String cid) throws Exception {
		String sql =" select count(*) from product where cid = ?";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		Long num =  (Long)queryRunner.query(sql, new ScalarHandler(),cid);
		return num.intValue();
	}

	@Override
	public List findProductsByCidWitPage(String cid, int startIndex, int pageSize) throws Exception {
		String sql = "select * from product where cid =? limit ? ,? ";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanListHandler<Product>(Product.class),cid,startIndex,pageSize);

	}

	@Override
	public int findTotalRecords() throws Exception {
		String sql =" select count(*) from product";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		Long num =  (Long)queryRunner.query(sql, new ScalarHandler());
		return num.intValue();	
	}

	@Override
	public List<Product> findAllProductsWithPage(int startIndex, int pageSize) throws Exception {
		String sql = "select * from product  order by pdate desc limit ? ,? ";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		return queryRunner.query(sql, new BeanListHandler<Product>(Product.class),startIndex,pageSize);

	}
	
}
